<?php
/**
 * Add new class
 * @param integer $coursesid
 * @param integer $teacherid
 * @param string $startdate
 * @param integer $learntime
 * @param string $note
 * @return boolean
 */
function seitcm_add_class($coursesid,$teacherid,$startdate,$learntime,$note,$alowreg){
	global $wpdb;
	try {
		$wpdb->query($wpdb->prepare('INSERT INTO ' . SEITCM_TABLE_CLASS . '(coursesid,classcode,teacherid,startdate,learntime,note,alowreg) VALUES (%d,%s,%d,%s,%d,%s,%d)',$coursesid,seitcm_create_next_class_code($coursesid),$teacherid,seitcm_create_date_for_mysql($startdate),$learntime,$note,$alowreg));
		return true;
	} catch (Exception $e) {
		return false;
	}
}
/**
 * 
 * @param integer $coursesid
 * @param integer $teacherid
 * @param string $startdate
 * @param integer $learntime
 * @param string $note
 * @param boolean $alowreg
 * @param array $studentlist
 * @return boolean
 */
function seitcm_add_class_with_student($coursesid,$teacherid,$startdate,$learntime,$note,$alowreg,$studentlist){
	global $wpdb;
	try {
		$wpdb->query('start transaction');
		if(!($wpdb->query($wpdb->prepare('INSERT INTO ' . SEITCM_TABLE_CLASS . 
											'(coursesid,classcode,teacherid,startdate,learntime,note,alowreg) VALUES (%d,%s,%d,%s,%d,%s,%d)',
											$coursesid,seitcm_create_next_class_code($coursesid),$teacherid,
											seitcm_create_date_for_mysql($startdate),$learntime,$note,$alowreg))))
		{
			$wpdb->query('rollback');
			return false;
		}
		
		$classid = seitcm_get_class_inserted_id($coursesid,$teacherid,$startdate,$learntime,$note,$alowreg);
		if(!seitcm_reg_list_student($studentlist, $classid))
		{
			$wpdb->query('rollback');
			return false;
		}
		$wpdb->query('commit');
		return true;
	} catch (Exception $e) {
		return false;
	}
}
function seitcm_get_class_inserted_id($coursesid,$teacherid,$startdate,$learntime,$note,$alowreg){
	global $wpdb;
	$sql = $wpdb->prepare('SELECT id FROM ' . SEITCM_TABLE_CLASS . 
											' WHERE coursesid = %d AND teacherid = %d AND startdate= %s AND learntime = %d AND alowreg = %d ORDER by id DESC',
											$coursesid,$teacherid,seitcm_create_date_for_mysql($startdate),$learntime,$alowreg);
	return $wpdb->get_var($sql);
}
function seitcm_create_next_class_code($coursesid)
{
	global $wpdb;
	try {
		$lastclass = $wpdb->get_row($wpdb->prepare('SELECT * FROM ' . SEITCM_TABLE_CLASS . ' WHERE coursesid = %d ORDER BY classcode DESC',$coursesid));
		if($lastclass) $lastclasscode = $lastclass->classcode;
		else $lastclasscode = 0;
	}
	catch (Exception $e){
		$lastclasscode = 0;
	}
	$lastclasscode++;
	switch (strlen($lastclasscode)){
		case 0:
			return '00000'.$lastclasscode;
			break;
		case 1:
			return '0000'.$lastclasscode;
			break;
		case 2:
			return '000'.$lastclasscode;
			break;
		case 3:
			return '00'.$lastclasscode;
			break;
		case 4:
			return '0'.$lastclasscode;
			break;
		case 5:
			return $lastclasscode;
			break;
		default:
			return $lastclasscode;
			
	}
}
/**
 * Update class
 * @param integer $coursesid
 * @param integer $teacherid
 * @param string $startdate
 * @param integer $learntime
 * @param string $note
 * @return boolean
 */
function seitcm_update_class($classid,$coursesid,$teacherid,$startdate,$learntime,$note,$alowreg,$finish){
	global $wpdb;
	try {
		if(seitcm_get_single_class($classid)->coursesid != $coursesid) {
			$wpdb->query($wpdb->prepare('UPDATE ' . SEITCM_TABLE_CLASS . ' SET coursesid = %d, classcode = %s, teacherid = %d, startdate = %s, learntime = %d, note = %s, alowreg = %d, finish = %d WHERE id = %d',$coursesid,seitcm_create_next_class_code($coursesid),$teacherid,seitcm_create_date_for_mysql($startdate),$learntime,$note,$alowreg,$finish,$classid));
		}
		else {
			$wpdb->query($wpdb->prepare('UPDATE ' . SEITCM_TABLE_CLASS . ' SET coursesid = %d, teacherid = %d, startdate = %s, learntime = %d, note = %s, alowreg = %d, finish = %d WHERE id = %d',$coursesid,$teacherid,seitcm_create_date_for_mysql($startdate),$learntime,$note,$alowreg,$finish,$classid));
		}
		return true;
	} catch (Exception $e) {
		return false;
	}
}
/**
 * Get Class List
 *
 * @param number $limit
 * @param number $offset
 * @return Ambigous <mixed, NULL, multitype:, multitype:multitype: , multitype:Ambigous <multitype:, NULL> >|NULL
 */
function seitcm_get_list_class($limit=10,$offset=0){
	global $wpdb;
	$sql = $wpdb->prepare('SELECT C.display_name, B.codename,A.alowreg,A.finish, A.classcode, B.name, B.note, A.startdate, A.learntime, A.id, A.note FROM '
. SEITCM_TABLE_CLASS .' A inner join '
. SEITCM_TABLE_COURSES .' B on A.coursesid = B.id
inner join '
. $wpdb->prefix.'users' .' C on A.teacherid = C.id ORDER BY `id` DESC LIMIT %d OFFSET %d',$limit,$offset);
	try{
		return $wpdb->get_results($sql);
	}
	catch (Exception $e) {
		return null;
	}
}
/**
 * Get Class List
 *
 * @param number $limit
 * @param number $offset
 * @return Ambigous <mixed, NULL, multitype:, multitype:multitype: , multitype:Ambigous <multitype:, NULL> >|NULL
 */
function seitcm_get_list_class_of_teacher($teacher,$limit=10,$offset=0){
	global $wpdb;
	$sql = $wpdb->prepare('SELECT C.display_name, B.codename,A.alowreg,A.finish, A.classcode, B.name, B.note, A.startdate, A.learntime, A.id, A.note FROM '
. SEITCM_TABLE_CLASS .' A inner join '
. SEITCM_TABLE_COURSES .' B on A.coursesid = B.id
inner join '
. $wpdb->prefix.'users' .' C on A.teacherid = C.id WHERE `teacherid` = %d ORDER BY `id` DESC LIMIT %d OFFSET %d',$teacher,$limit,$offset);
	try{
		return $wpdb->get_results($sql);
	}
	catch (Exception $e) {
		return null;
	}
}
/**
 * Get count total class
 *
 * @return number
 */
function seitcm_get_count_class(){
	global $wpdb;
	$sql = 'SELECT count(id) FROM ' . SEITCM_TABLE_CLASS;
	try{
		return $wpdb->get_var($sql);
	}
	catch (Exception $e) {
		return 0;
	}
}
function seitcm_get_count_class_of_teacher($teacher){
	global $wpdb;
	$sql = $wpdb->prepare('SELECT count(id) FROM ' . SEITCM_TABLE_CLASS . ' WHERE `teacherid` = %d',$teacher);
	try{
		return $wpdb->get_var($sql);
	}
	catch (Exception $e) {
		return 0;
	}
}
/**
 * 
 * @param unknown $id
 * @return Ambigous <mixed, NULL, multitype:>|number
 */
function seitcm_get_single_class($id)
{
	global $wpdb;
	$sql = 'SELECT *,A.note AS classnote,B.note AS coursesnote,A.id AS classid FROM '
	. SEITCM_TABLE_CLASS .' A INNER JOIN '
	. SEITCM_TABLE_COURSES .' B ON A.coursesid = B.id INNER JOIN '
	. $wpdb->prefix.'users' .' C ON A.teacherid = C.id WHERE A.id = ' . $id;
	try{
		return $wpdb->get_row($sql);
	}
	catch (Exception $e) {
		return 0;
	}
}

function seitcm_check_exist_class($id){
	global $wpdb;
	$wpdb->get_results($wpdb->prepare('SELECT id FROM ' . SEITCM_TABLE_CLASS . ' WHERE id = %s',$id));
	if($wpdb->num_rows == 0) return false;
	return true;
}
/**
 * Get Class list can be registing
 *
 * @param number $limit
 * @param number $offset
 * @return Ambigous <mixed, NULL, multitype:, multitype:multitype: , multitype:Ambigous <multitype:, NULL> >|NULL
 */
function seitcm_get_list_class_for_reg(){
	global $wpdb;
	$sql = 'SELECT C.display_name, B.codename, B.name, B.note, A.startdate, A.learntime, A.id,A.classcode,A.alowreg,A.finish, A.note FROM '
			. SEITCM_TABLE_CLASS .' A inner join '
			. SEITCM_TABLE_COURSES .' B on A.coursesid = B.id inner join '
			. $wpdb->prefix.'users' .' C on A.teacherid = C.id WHERE A.alowreg=1 ORDER BY `learntime` DESC';
	try{
		return $wpdb->get_results($sql);
	}
	catch (Exception $e) {
		return null;
	}
}
/**
 *
 * @param number $limit
 * @param number $offset
 * @return Ambigous <mixed, NULL, multitype:, multitype:multitype: , multitype:Ambigous <multitype:, NULL> >|NULL
 */
function seitcm_get_list_class_present(){
	global $wpdb;
	$sql = 'SELECT C.display_name, B.codename, B.name, B.note, A.startdate, A.learntime, A.id, A.classcode, A.alowreg, A.finish, A.note FROM '
			. SEITCM_TABLE_CLASS .' A inner join '
			. SEITCM_TABLE_COURSES .' B on A.coursesid = B.id inner join '
			. $wpdb->prefix.'users' .' C on A.teacherid = C.id WHERE A.finish=0 ORDER BY `classcode` DESC';
	try{
		return $wpdb->get_results($sql);
	}
	catch (Exception $e) {
		return null;
	}
}
/**
 * 
 * @param unknown $id
 * @return Ambigous <string, NULL>|NULL
 */
function seitcm_get_fees_of_class($id)
{
	global $wpdb;
	try {
		$curdate = date('Y-m-d H:i:s',mktime(date("H")+7,date("i"),date("s"),date("m"),date("d"),date("Y")));
		return  $wpdb->get_var($wpdb->prepare('SELECT cost FROM ' . SEITCM_TABLE_FEES . ' WHERE `coursesid` = (SELECT `coursesid` FROM ' . SEITCM_TABLE_CLASS . ' WHERE id = %d) AND `applysince` <= %s ORDER BY `applysince` DESC',$id,$curdate));
	}
	catch (Exception $e){
		return null;
	}
}
/**
 * 
 * @param unknown $id
 * @return Ambigous <mixed, NULL, multitype:, multitype:multitype: , multitype:Ambigous <multitype:, NULL> >
 */
function seitcm_get_class_student_list($id)
{
	global $wpdb;
	$sql = $wpdb->prepare('SELECT * FROM ' . $wpdb->prefix . 'users A INNER JOIN ' . SEITCM_TABLE_REGISTER .' B ON A.ID = B.studentid WHERE B.classid = %d ',$id);
	return $wpdb->get_results($sql);
}
/**
 * 
 * @param number $learntime
 * @param string $datestring
 * @return boolean
 */
function seitcm_check_date_is_in_learntime($learntime,$datestring)
{
	global $seitcm_learntime;
	foreach ($seitcm_learntime as $learntimes)
	{
		if($learntimes['key'] == $learntime) 
		{
			$parts = explode(',', $learntimes['Value']);
			foreach ($parts as $part)
			{
				if($part==seitcm_get_DOW($datestring))
				{
					return true;
				}
			}
		}
	}
	return false;
}
function seitcm_get_count_student_in_class($classid){
	global $wpdb;
	$sql = $wpdb->prepare('SELECT COUNT(ID) FROM ' . SEITCM_TABLE_REGISTER . ' WHERE classid = %d',$classid);
	return $wpdb->get_var($sql);
}
function seitcm_get_class_list_learned($studentid){
	global $wpdb;
	$sql = $wpdb->prepare('SELECT A.id,A.startdate,A.classcode,C.codename FROM ' . SEITCM_TABLE_CLASS . ' A INNER JOIN ' . SEITCM_TABLE_REGISTER . ' B ON
									A.id = B.classid INNER JOIN ' . SEITCM_TABLE_COURSES . ' C ON
									A.coursesid = C.id WHERE B.studentid = %d',$studentid);
	return $wpdb->get_results($sql);
}
/**
 * 
 * @param number $classid
 * @return Ambigous <string, NULL>
 */
function seitcm_get_class_finish_time($classid){
	global $wpdb;
	$sql = $wpdb->prepare('SELECT `time` FROM '.SEITCM_TABLE_SCHEDULE.' WHERE `classid` = %d ORDER BY `time` DESC LIMIT 1',$classid);
	return $wpdb->get_var($sql);
}
/**
 * 
 * @param unknown $idclass1
 * @param unknown $idclass2
 * @return Ambigous <mixed, NULL, multitype:, multitype:multitype: , multitype:Ambigous <multitype:, NULL> >
 */
function seitcm_class_comparison_student($idclass1,$idclass2){
	global $wpdb;
	$sql = $wpdb->prepare('SELECT * FROM '.SEITCM_TABLE_REGISTER.' WHERE classid = %d OR classid = %d',$idclass1,$idclass2);
	return $wpdb->get_results($sql);
}
function seitcm_class_student_filter($idclass_get,$idclass_skip){
	global $wpdb;
	$sql = $wpdb->prepare('SELECT * FROM '.SEITCM_TABLE_REGISTER.' WHERE `classid` = %d AND `studentid` NOT IN (
								SELECT studentid FROM '.SEITCM_TABLE_REGISTER.' WHERE classid = %d
								)',$idclass_get,$idclass_skip);
	return $wpdb->get_results($sql);
}
function seitcm_class_student_common($idclass_1,$idclass_2){
	global $wpdb;
	$sql = $wpdb->prepare('SELECT * FROM '.SEITCM_TABLE_REGISTER.' WHERE `classid` = %d AND `studentid` IN (
				SELECT studentid FROM '.SEITCM_TABLE_REGISTER.' WHERE classid = %d
			)',$idclass_1,$idclass_2);
	return $wpdb->get_results($sql);
}
function seitcm_get_class_name($classid){
	global $wpdb;
	$sql = 'SELECT CONCAT(B.codename,\'.\',A.classcode) as classname FROM '
			. SEITCM_TABLE_CLASS .' A INNER JOIN '
					. SEITCM_TABLE_COURSES .' B ON A.coursesid = B.id INNER JOIN '
							. $wpdb->prefix.'users' .' C ON A.teacherid = C.id WHERE A.id = ' . $classid;
	try{
		return $wpdb->get_var($sql);
	}
	catch (Exception $e) {
		return 0;
	}
}
?>